/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sys.bean.EmpresaBean;
import sys.bean.VeiculoBean;
import sys.utils.ConexaoMySql;

/**
 *
 * @author Administrador
 */
public class VeiculoDao {

    public void alterar(VeiculoBean veiculo) throws SQLException {

        Connection con = ConexaoMySql.getConection();

        String sql = "UPDATE veiculo SET \n"
                + "empresa_id_empresa=?, modelo=?, placa=?, ano=?, renavam=?, chassi=?, obs=? \n"
                + "WHERE  id_veiculo=?;";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setInt(1, veiculo.getEmpresa().getIdEmpresa());
        stmt.setString(2, veiculo.getModelo());
        stmt.setString(3, veiculo.getPlaca());
        stmt.setInt(4, veiculo.getAno());
        stmt.setString(5, veiculo.getRenavam());
        stmt.setString(6, veiculo.getChassi());
        stmt.setString(7, veiculo.getObs());
        stmt.setInt(8, veiculo.getIdVeiculo());

        stmt.execute();
        con.close();
        stmt.close();

    }

    public void incluir(VeiculoBean veiculo) throws SQLException {

        Connection con = ConexaoMySql.getConection();
        String sql = "INSERT INTO veiculo \n"
                + "(empresa_id_empresa, modelo, placa, ano, renavam, chassi, obs) \n"
                + "VALUES \n"
                + "(?, ?, ?, ?, ?, ?, ?);";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setInt(1, veiculo.getEmpresa().getIdEmpresa());
        stmt.setString(2, veiculo.getModelo());
        stmt.setString(3, veiculo.getPlaca());
        stmt.setInt(4, veiculo.getAno());
        stmt.setString(5, veiculo.getRenavam());
        stmt.setString(6, veiculo.getChassi());
        stmt.setString(7, veiculo.getObs());

        stmt.execute();

        stmt.close();
        con.close();
    }

    public VeiculoBean consultaVeiculo(int idVeiculo) throws SQLException {

        Connection con = ConexaoMySql.getConection();

        String sql = "SELECT v.id_veiculo, v.modelo, v.placa, v.ano, v.renavam, v.chassi, v.obs, e.id_empresa, e.razao_social \n"
                + "FROM veiculo v \n"
                + "INNER JOIN empresa e ON (v.empresa_id_empresa = e.id_empresa) \n"
                + "AND v.id_veiculo = " + idVeiculo;

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        EmpresaBean empresa = new EmpresaBean();
        VeiculoBean veiculo = new VeiculoBean();

        while (rs.next()) {

            veiculo.setIdVeiculo(rs.getInt("id_veiculo"));
            veiculo.setModelo(rs.getString("modelo"));
            veiculo.setPlaca(rs.getString("placa"));
            veiculo.setAno(rs.getInt("ano"));
            veiculo.setRenavam(rs.getString("renavam"));
            veiculo.setChassi(rs.getString("chassi"));
            veiculo.setObs(rs.getString("obs"));

            empresa.setIdEmpresa(rs.getInt("id_empresa"));
            empresa.setRazaoSocial(rs.getString("razao_social"));

            veiculo.setEmpresa(empresa);
        }

        stmt.close();
        con.close();

        return veiculo;
    }

    public List<VeiculoBean> listaVeiculos(String desc) throws SQLException {
        List<VeiculoBean> listVeiculos = new ArrayList<>();
        Connection con = ConexaoMySql.getConection();

        String sql = "SELECT v.id_veiculo, v.modelo, v.placa\n"
                + "FROM veiculo v\n"
                + "WHERE v.modelo LIKE '%" + desc + "%'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            VeiculoBean veiculo = new VeiculoBean();

            veiculo.setIdVeiculo(rs.getInt("id_veiculo"));
            veiculo.setModelo(rs.getString("modelo"));
            veiculo.setPlaca(rs.getString("placa"));

            listVeiculos.add(veiculo);
        }

        stmt.close();
        con.close();

        return listVeiculos;
    }

}
